#coding=utf-8
import pymysql
import time
class dbmysql():
    #构造函数
    def __init__(self, host="111.231.218.254", user='root',
                         password='a123456+', db='testpy', port=3306,charset='utf8'):
        self.host = host
        self.user = user
        self.password = password
        self.db = db
        self.port = port
        self.charset = charset
        self.conn = None
        self.cur = None
    #连接数据库
    def connDB(self):
        try:
            #print(self.host, self.user, self.password, self.db, self.port, self.charset)
            self.conn = pymysql.connect(host = self.host, user = self.user, password = self.password,
                                      db = self.db, port = self.port, charset = self.charset)
            #print("---数据库连接成功-----")
            #pymysql.charset="UTF-8"
            #conn.select_db('dbnaself')
        except Exception as e:
            print('连接失败...' + e)
            raise e
            return False
        self.cur = self.conn.cursor()
        return True
    #关闭数据库
    def close(self):
        if self.conn and self.cur:
            self.conn.close()
            self.cur.close()
        return True
    #excecute
    def execute(self, sql, params=None):
        self.connDB()
        try:
            if self.conn and self.cur:
                self.cur.execute(sql, params)
                self.conn.commit()
        except:
            self.close()
            return False
        return True
    #查询
    def query(self,sql):
        self.connDB()
        try:
            self.cur.execute(sql)
            results = self.cur.fetchall()
            for row in results:
                print(row)
        except Exception as e:
            raise e #raise Expcetion('异常')
        finally:
            self.close()
    #insert
    def insert(self, sql):
        self.connDB()
        try:
            self.cur.execute(sql)
            #提交
            self.conn.commit()
        except Exception as e:
            #错误回滚
            self.conn.rollback()
            raise Exception('插入失败\n' + e)
        finally:
            self.close()
    def batchInsert(self, sql, params):
        self.connDB()
        start = time.strftime("%M:%S")
        try:
            self.cur.executemany(sql, params)
            # 提交
            self.conn.commit()
        except Exception as e:
            # 错误回滚
            self.conn.rollback()
            raise Exception('插入失败\n' + e)
        finally:
            self.close()
        end = time.strftime("%M:%S")
        print("批量添加完毕...")
    #update 和 insert 类似
    def update(self, sql):
        self.connDB()
        try:
            self.cur.execute(sql)    #传参  需要创表时指定UTF-8
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e
        finally:
            self.close()
    #delete
    def delete(self, sql):
        self.connDB()
        try:
            self.cur.execute(sql)    #传参  需要创表时指定UTF-8
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e
        finally:
            self.close()
        # query("select * from movie")
        # insert("""insert into movie values('3','333','4444',999)""")
        #query("""select * from movie where id=999""")
        # update("""update movie set naself='%s' where id = %d""")
        # delete("""delete from movie where id=%d""")

if __name__ == '__main__':
    print('DBUTIL')
    dbutil = dbmysql()


